﻿using Omabar.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Tom.Net;

namespace Omabar.Entity
{
    public class Route_Entity : EntityBase<ROUTE>
    {
        public override RESP_Info Add(ROUTE model)
        {
            RESP_Info info = new RESP_Info();
            using (SqlConnection conn = new SqlConnection(WebConfigReader.ReadConnectString("OmabarEntities")))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "insert into ROUTE(ROUTE_ID,ROUTE_NAME,ROUTE_DES,ROUTE_CHENGDU,ROUTE_BEIJING,ROUTE_ELSECITY,ROUTE_CHARACTERISTIC,ROUTE_DAYS,ROUTE_PRICE,ROUTE_DATE_START,ROUTE_DATE_END,ROUTE_HOT,ROUTE_BAK,ROUTE_ELABORATE,ROUTE_ORIGINAL) values (@ROUTE_ID,@ROUTE_NAME,@ROUTE_DES,@ROUTE_CHENGDU,@ROUTE_BEIJING,@ROUTE_ELSECITY,@ROUTE_CHARACTERISTIC,@ROUTE_DAYS,@ROUTE_PRICE,@ROUTE_DATE_START,@ROUTE_DATE_END,@ROUTE_HOT,@ROUTE_BAK,@ROUTE_ELABORATE,@ROUTE_ORIGINAL)";
                    cmd.CommandType = System.Data.CommandType.Text;

                    cmd.Parameters.Add(new SqlParameter("ROUTE_ID", model.ROUTE_ID));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_NAME", model.ROUTE_NAME));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_DES", model.ROUTE_DES));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_CHENGDU", model.ROUTE_CHENGDU));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_BEIJING", model.ROUTE_BEIJING));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_ELSECITY", model.ROUTE_ELSECITY));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_CHARACTERISTIC", model.ROUTE_CHARACTERISTIC));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_DAYS", model.ROUTE_DAYS));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_PRICE", model.ROUTE_PRICE));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_DATE_START", model.ROUTE_DATE_START));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_DATE_END", model.ROUTE_DATE_END));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_HOT", model.ROUTE_HOT));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_BAK", model.ROUTE_BAK));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_ELABORATE", model.ROUTE_ELABORATE));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_ORIGINAL", model.ROUTE_ORIGINAL));

                    cmd.ExecuteNonQuery();

                    info.Success = true;
                    info.Title = "成功";
                    info.Tip = "<h5><p>添加旅程成功</p></h5>";
                    info.Url = "RouteList.aspx";
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    info.Success = false;
                    info.Title = "错误";
                    info.Tip = "<h5><p>添加旅程失败</p></h5>";
                    info.Url = "RouteAdd.aspx";

                    Tom.Net.Log.LogManager.Error("添加旅程失败", ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return info;
        }

        public override RESP_Info Update(ROUTE model)
        {
            RESP_Info info = new RESP_Info();
            using (SqlConnection conn = new SqlConnection(WebConfigReader.ReadConnectString("OmabarEntities")))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "update ROUTE set ROUTE_NAME=@ROUTE_NAME,ROUTE_DES=@ROUTE_DES,ROUTE_CHENGDU=@ROUTE_CHENGDU,ROUTE_BEIJING=@ROUTE_BEIJING,ROUTE_ELSECITY=@ROUTE_ELSECITY,ROUTE_CHARACTERISTIC=@ROUTE_CHARACTERISTIC,ROUTE_DAYS=@ROUTE_DAYS,ROUTE_PRICE=@ROUTE_PRICE,ROUTE_DATE_START=@ROUTE_DATE_START,ROUTE_DATE_END=@ROUTE_DATE_END,ROUTE_HOT=@ROUTE_HOT,ROUTE_BAK=@ROUTE_BAK,ROUTE_ELABORATE=@ROUTE_ELABORATE,ROUTE_ORIGINAL=@ROUTE_ORIGINAL  where ROUTE_ID=@ROUTE_ID";
                    cmd.CommandType = System.Data.CommandType.Text;

                    cmd.Parameters.Add(new SqlParameter("ROUTE_ID", model.ROUTE_ID));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_NAME", model.ROUTE_NAME));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_DES", model.ROUTE_DES));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_CHENGDU", model.ROUTE_CHENGDU));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_BEIJING", model.ROUTE_BEIJING));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_ELSECITY", model.ROUTE_ELSECITY));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_CHARACTERISTIC", model.ROUTE_CHARACTERISTIC));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_DAYS", model.ROUTE_DAYS));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_PRICE", model.ROUTE_PRICE));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_DATE_START", model.ROUTE_DATE_START));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_DATE_END", model.ROUTE_DATE_END));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_HOT", model.ROUTE_HOT));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_BAK", model.ROUTE_BAK));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_ELABORATE", model.ROUTE_ELABORATE));
                    cmd.Parameters.Add(new SqlParameter("ROUTE_ORIGINAL", model.ROUTE_ORIGINAL));

                    cmd.ExecuteNonQuery();

                    info.Success = true;
                    info.Title = "编辑";
                    info.Tip = "<h5><p>编辑旅程成功</p></h5>";
                    info.Url = "RouteList.aspx";
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    info.Success = true;
                    info.Title = "错误";
                    info.Tip = "<h5><p>编辑旅程失败</p></h5>";
                    info.Url = "RouteAdd.aspx";

                    Tom.Net.Log.LogManager.Error("编辑旅程失败", ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return info;
        }

        public override RESP_Info Delete(ROUTE route)
        {
            RESP_Info info = new RESP_Info();
            using (SqlConnection conn = new SqlConnection(WebConfigReader.ReadConnectString("OmabarEntities")))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "delete from ROUTE where ROUTE_ID=@ROUTE_ID";
                    cmd.CommandType = System.Data.CommandType.Text;

                    cmd.Parameters.Add(new SqlParameter("ROUTE_ID", route.ROUTE_ID));
                    cmd.ExecuteNonQuery();

                    info.Success = true;
                    info.Title = "删除";
                    info.Tip = "<h5><p>删除旅程成功</p></h5>";
                    info.Url = "RouteList.aspx";
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    info.Success = true;
                    info.Title = "错误";
                    info.Tip = "<h5><p>删除旅程失败</p></h5>";
                    info.Url = "RouteList.aspx";

                    Tom.Net.Log.LogManager.Error("删除旅程失败", ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return info;
        }

        public override ROUTE GetModel(string id)
        {
            using (SqlConnection conn = new SqlConnection(WebConfigReader.ReadConnectString("OmabarEntities")))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "select  ROUTE_ID,ROUTE_NAME,ROUTE_DES,ROUTE_CHENGDU,ROUTE_BEIJING,ROUTE_ELSECITY,ROUTE_CHARACTERISTIC,ROUTE_DAYS,ROUTE_PRICE,ROUTE_DATE_START,ROUTE_DATE_END,ROUTE_HOT,ROUTE_BAK,ROUTE_ELABORATE,ROUTE_ORIGINAL from ROUTE where ROUTE_ID=@ROUTE_ID";
                    cmd.CommandType = System.Data.CommandType.Text;

                    cmd.Parameters.Add(new SqlParameter("ROUTE_ID", id));

                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        ROUTE route = new ROUTE();
                        if (!reader.IsDBNull(0)) route.ROUTE_ID = reader.GetString(0);
                        if (!reader.IsDBNull(1)) route.ROUTE_NAME = reader.GetString(1);
                        if (!reader.IsDBNull(2)) route.ROUTE_DES = reader.GetString(2);
                        if (!reader.IsDBNull(3)) route.ROUTE_CHENGDU = reader.GetInt32(3);
                        if (!reader.IsDBNull(4)) route.ROUTE_BEIJING = reader.GetInt32(4);
                        if (!reader.IsDBNull(5)) route.ROUTE_ELSECITY = reader.GetString(5);
                        if (!reader.IsDBNull(6)) route.ROUTE_CHARACTERISTIC = reader.GetString(6);
                        if (!reader.IsDBNull(7)) route.ROUTE_DAYS = reader.GetInt16(7);
                        if (!reader.IsDBNull(8)) route.ROUTE_PRICE = reader.GetInt32(8);
                        if (!reader.IsDBNull(9)) route.ROUTE_DATE_START = reader.GetDateTime(9);
                        if (!reader.IsDBNull(10)) route.ROUTE_DATE_END = reader.GetDateTime(10);
                        if (!reader.IsDBNull(11)) route.ROUTE_HOT = reader.GetInt16(11);
                        if (!reader.IsDBNull(12)) route.ROUTE_BAK = reader.GetString(12);
                        if (!reader.IsDBNull(13)) route.ROUTE_ELABORATE = reader.GetInt16(13);
                        if (!reader.IsDBNull(14)) route.ROUTE_ORIGINAL = reader.GetInt16(14);

                        route.Themes = new Theme_Entity().GetModels(string.Format("SELECT THEME_ID,THEME_NAME,THEME_DES FROM THEME WHERE THEME_ID IN (SELECT THEME_ID FROM ROUTE_THEME WHERE ROUTE_ID='{0}')", route.ROUTE_ID));
                        route.Photo = new Photo_Entity().GetModelBySQL(string.Format("SELECT TOP 1 PHOTO_ID,PHOTO_NAME,PHOTO_URL,PHOTO_DES,PHOTO_HOT,PHOTO_COVER FROM PHOTO WHERE PHOTO_ID IN (SELECT PHOTO_ID FROM ROUTE_GALLERY WHERE ROUTE_ID='{0}') AND PHOTO_COVER=1", route.ROUTE_ID));
                        reader.Close();
                        reader.Dispose();
                        cmd.Dispose();
                        return route;
                    }
                }
                catch (Exception ex)
                {
                    Tom.Net.Log.LogManager.Error("获取旅程数据失败", ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return null;
        }

        public override ROUTE GetModelBySQL(string sql)
        {
            using (SqlConnection conn = new SqlConnection(WebConfigReader.ReadConnectString("OmabarEntities")))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    cmd.CommandType = System.Data.CommandType.Text;

                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        ROUTE route = new ROUTE();
                        if (!reader.IsDBNull(0)) route.ROUTE_ID = reader.GetString(0);
                        if (!reader.IsDBNull(1)) route.ROUTE_NAME = reader.GetString(1);
                        if (!reader.IsDBNull(2)) route.ROUTE_DES = reader.GetString(2);
                        if (!reader.IsDBNull(3)) route.ROUTE_CHENGDU = reader.GetInt32(3);
                        if (!reader.IsDBNull(4)) route.ROUTE_BEIJING = reader.GetInt32(4);
                        if (!reader.IsDBNull(5)) route.ROUTE_ELSECITY = reader.GetString(5);
                        if (!reader.IsDBNull(6)) route.ROUTE_CHARACTERISTIC = reader.GetString(6);
                        if (!reader.IsDBNull(7)) route.ROUTE_DAYS = reader.GetInt16(7);
                        if (!reader.IsDBNull(8)) route.ROUTE_PRICE = reader.GetInt32(8);
                        if (!reader.IsDBNull(9)) route.ROUTE_DATE_START = reader.GetDateTime(9);
                        if (!reader.IsDBNull(10)) route.ROUTE_DATE_END = reader.GetDateTime(10);
                        if (!reader.IsDBNull(11)) route.ROUTE_HOT = reader.GetInt16(11);
                        if (!reader.IsDBNull(12)) route.ROUTE_BAK = reader.GetString(12);
                        if (!reader.IsDBNull(13)) route.ROUTE_ELABORATE = reader.GetInt16(13);
                        if (!reader.IsDBNull(14)) route.ROUTE_ORIGINAL = reader.GetInt16(14);

                        route.Themes = new Theme_Entity().GetModels(string.Format("SELECT THEME_ID,THEME_NAME,THEME_DES FROM THEME WHERE THEME_ID IN (SELECT THEME_ID FROM ROUTE_THEME WHERE ROUTE_ID='{0}')", route.ROUTE_ID));
                        route.Photo = new Photo_Entity().GetModelBySQL(string.Format("SELECT TOP 1 PHOTO_ID,PHOTO_NAME,PHOTO_URL,PHOTO_DES,PHOTO_HOT,PHOTO_COVER FROM PHOTO WHERE PHOTO_ID IN (SELECT PHOTO_ID FROM ROUTE_GALLERY WHERE ROUTE_ID='{0}') AND PHOTO_COVER=1", route.ROUTE_ID));

                        reader.Close();
                        reader.Dispose();
                        cmd.Dispose();
                        return route;
                    }
                }
                catch (Exception ex)
                {
                    Tom.Net.Log.LogManager.Error("获取旅程数据失败", ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return null;
        }

        public override List<ROUTE> GetModels(string sql)
        {
            using (SqlConnection conn = new SqlConnection(WebConfigReader.ReadConnectString("OmabarEntities")))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    cmd.CommandType = System.Data.CommandType.Text;

                    SqlDataReader reader = cmd.ExecuteReader();
                    List<ROUTE> routes = new List<ROUTE>();
                    while (reader.Read())
                    {
                        ROUTE route = new ROUTE();
                        if (!reader.IsDBNull(0)) route.ROUTE_ID = reader.GetString(0);
                        if (!reader.IsDBNull(1)) route.ROUTE_NAME = reader.GetString(1);
                        if (!reader.IsDBNull(2)) route.ROUTE_DES = reader.GetString(2);
                        if (!reader.IsDBNull(3)) route.ROUTE_CHENGDU = reader.GetInt32(3);
                        if (!reader.IsDBNull(4)) route.ROUTE_BEIJING = reader.GetInt32(4);
                        if (!reader.IsDBNull(5)) route.ROUTE_ELSECITY = reader.GetString(5);
                        if (!reader.IsDBNull(6)) route.ROUTE_CHARACTERISTIC = reader.GetString(6);
                        if (!reader.IsDBNull(7)) route.ROUTE_DAYS = reader.GetInt16(7);
                        if (!reader.IsDBNull(8)) route.ROUTE_PRICE = reader.GetInt32(8);
                        if (!reader.IsDBNull(9)) route.ROUTE_DATE_START = reader.GetDateTime(9);
                        if (!reader.IsDBNull(10)) route.ROUTE_DATE_END = reader.GetDateTime(10);
                        if (!reader.IsDBNull(11)) route.ROUTE_HOT = reader.GetInt16(11);
                        if (!reader.IsDBNull(12)) route.ROUTE_BAK = reader.GetString(12);
                        if (!reader.IsDBNull(13)) route.ROUTE_ELABORATE = reader.GetInt16(13);
                        if (!reader.IsDBNull(14)) route.ROUTE_ORIGINAL = reader.GetInt16(14);

                        route.Themes = new Theme_Entity().GetModels(string.Format("SELECT THEME_ID,THEME_NAME,THEME_DES FROM THEME WHERE THEME_ID IN (SELECT THEME_ID FROM ROUTE_THEME WHERE ROUTE_ID='{0}')", route.ROUTE_ID));
                        route.ROUTE_COUNTRYs = new Route_Country_Entity().GetModels(string.Format("select ROUTE_ID,ROUTE_CONTINENT,ROUTE_COUNTRY_NAME,ROUTE_CITY from ROUTE_COUNTRY WHERE ROUTE_ID='{0}'", route.ROUTE_ID));
                        route.Photo = new Photo_Entity().GetModelBySQL(string.Format("SELECT TOP 1 PHOTO_ID,PHOTO_NAME,PHOTO_URL,PHOTO_DES,PHOTO_HOT,PHOTO_COVER FROM PHOTO WHERE PHOTO_ID IN (SELECT PHOTO_ID FROM ROUTE_GALLERY WHERE ROUTE_ID='{0}') AND PHOTO_COVER=1", route.ROUTE_ID));

                        routes.Add(route);
                    }
                    reader.Close();
                    reader.Dispose();
                    cmd.Dispose();

                    return routes;
                }
                catch (Exception ex)
                {
                    Tom.Net.Log.LogManager.Error("获取旅程数据失败", ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return null;
        }
    }
}